package org.neuedu.dao;

import org.neuedu.bean.*;
import org.neuedu.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public int serchUserByRegEmail(String email) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select count(*) counts from user where email = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, email);
            rs = ps.executeQuery();
            if(rs.next()){
                count = rs.getInt("counts");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public int saveUserInfo(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "insert into user(email,password,nickname) values (?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getEmail());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getNickName());
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public int saveRoleUserInfo(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "insert into role_user(uid,rid) values (?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, user.getId());
            ps.setInt(2, 2);
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public User serchUidByEmail(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User u = null;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select id from user where email = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getEmail());
            rs = ps.executeQuery();
            if(rs.next()){
                u = new User();
                u.setId(rs.getInt("id"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return u;
    }

    @Override
    public User serchUserByEmailAndPass(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User u = null;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select * from user where email = ? and password = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getEmail());
            ps.setString(2, user.getPassword());
            rs = ps.executeQuery();
            while (rs.next()){
                u = new User();
                u.setId(rs.getInt("id"));
                u.setEmail(user.getEmail());
                u.setPassword(user.getPassword());
                u.setRegTime(rs.getDate("regtime"));
                u.setNickName(rs.getString("nickname"));
                u.setAvatar(rs.getString("avatar"));
                u.setCity(rs.getString("city"));
                u.setEnable(rs.getBoolean("enable"));
                u.setGender(rs.getBoolean("gender"));
                u.setSign(rs.getString("sign"));
                u.setKissNum(rs.getInt("kissnum"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return u;
    }

    @Override
    public List<Role> serchRolesByUid(Integer uid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User u = null;
        List<Role> roles = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select r.id id,r.name name,r.namezh namezh\n" +
                    "from role r\n" +
                    "join role_user ru\n" +
                    "on r.id = ru.rid\n" +
                    "where uid = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uid);
            rs = ps.executeQuery();
            while (rs.next()) {
                Role role = new Role();
                role.setId(rs.getInt("id"));
                role.setName(rs.getString("name"));
                role.setNameZh(rs.getString("namezh"));
                roles.add(role);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return roles;
    }
    @Override
    public User updateKissnum(Integer kiss,Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        User user = new User();
        try{
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update user set kissnum = kissnum - ? where id = ? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,kiss);
            ps.setInt(2,id);
            ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return user;
    }
    @Override
    public int qd(Integer id) {
        // TODO Auto-generated method stub
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update user set hitcard=1,kissnum=kissnum+5 where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public boolean userhitcard(int id) {
        // TODO Auto-generated method stub
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        boolean x=false;
        int y=0;
        int z=0;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select hitcard from user where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if(rs.next()){
                z=rs.getInt("hitcard");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        if(y==z){
            x=true;
        }
        return x;
    }
    @Override
    public List<Article> userArticle(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select * from article where uid = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                Article article = new Article();
                article.setId(rs.getInt("id"));
                article.setTitle(rs.getString("title"));
                article.setReplyNum(rs.getInt("replynum"));
                article.setCream(rs.getBoolean("iscream"));
                article.setPublishTime(rs.getString("publishtime"));
                article.setViews(rs.getInt("views"));
                list.add(article);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public List<Reply> userReply(Integer uid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Reply> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select title,replyTime,replyContent,r.aid aid " +
                    "from reply r join article a " +
                    "on r.aid = a.id " +
                    "where r.uid = ? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uid);
            rs = ps.executeQuery();
            while (rs.next()){
                Reply reply = new Reply();
                Article article = new Article();
                article.setTitle(rs.getString("title"));
                reply.setArticle(article);
                reply.setReplyContent(rs.getString("replyContent"));
                reply.setReplyTime(rs.getString("replyTime"));
                reply.setAid(rs.getInt("aid"));
                list.add(reply);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public User serchUserset(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User u = null;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select email,nickname,gender,city,sign from user where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                u = new User();
                u.setEmail(rs.getString("email"));
                u.setNickName(rs.getString("nickname"));
                u.setCity(rs.getString("city"));
                u.setGender(rs.getBoolean("gender"));
                u.setSign(rs.getString("sign"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return u;
    }

    @Override
    public int updateUserset(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int i = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update user set email =?,gender =?,nickname=?,city = ?,sign =? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getEmail());
            ps.setBoolean(2,user.getGender());
            ps.setString(3, user.getNickName());
            ps.setString(4,user.getCity());
            ps.setString(5, user.getSign());
            ps.setInt(6, user.getId());
            i = ps.executeUpdate();
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return i;
    }
    @Override

    public List<Problem> getTwInfo() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Problem> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "SELECT DATE_FORMAT(replytime,'%Y-%m-%d') replytime,replycontent,iscream,c.id cid,title,DATE_FORMAT(publishtime,'%Y-%m-%d') publishtime,views,replynum,catenameZh from category c \n" +
                    "join article a on c.id=a.cid join user u on u.id=a.uid join reply r on r.aid=a.id where cid=1;";
            ps = conn.prepareStatement(sql);

            rs = ps.executeQuery();
            while (rs.next()) {
                Problem problem =new Problem();
                problem.setReplyTime(rs.getString("replytime"));
                problem.setReplyContent(rs.getString("replycontent"));
                problem.setIscream(rs.getBoolean("iscream"));
                problem.setId(rs.getInt("cid"));
                problem.setTitle(rs.getString("title"));
                problem.setPublishTime(rs.getString("publishTime"));
                problem.setViews(rs.getInt("views"));
                problem.setCateNameZh(rs.getString("catenamezh"));
                problem.setReplynum(rs.getInt("replynum"));
                list.add(problem);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public User updatepassset(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User u = null;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select password from user where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                u = new User();
                u.setPassword(rs.getString("password"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return u;
    }

    @Override
    public int updatepasssets(Integer id,String pass) {
        Connection conn = null;
        PreparedStatement ps = null;
        int i = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update user set password = ? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, pass);
            ps.setInt(2, id);
            i = ps.executeUpdate();
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return i;
    }
    @Override
    public int updateSetUpAvatar(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int i = 0;
        try{
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update user set avatar = ? where email = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getAvatar());
            ps.setString(2,user.getEmail());
            i = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return i;
    }
}